/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package uniriotec.pm.prova.dao.postgresql;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import org.apache.log4j.Logger;
import uniriotec.pm.prova.dao.DisciplinaDAO;
import uniriotec.pm.prova.dto.DisciplinaDTO;
import uniriotec.pm.prova.dto.TopicoDTO;
import uniriotec.pm.prova.model.Disciplina;
import uniriotec.pm.prova.model.Topico;

/**
 *
 * @author albertoapr
 */
public class PostgresqlDisciplinaDAO extends PostgresqlDB implements DisciplinaDAO {
	/** The Constant LOG. */
	 private static final Logger LOG = Logger.getLogger(PostgresqlDisciplinaDAO.class);
    @Override
    public void create(DisciplinaDTO disciplina) 
            {
       try {
            String sql = "insert into disciplina select nextval('disciplina_id_seq') as id,? as codigo, ? as nome";
            this.criaConexao(true);
            PreparedStatement stmt = null;
            stmt = this.getConnection().prepareStatement(sql);
            stmt.setString(1, disciplina.getCodigo());
	    stmt.setString(2, disciplina.getNome());
            stmt.execute();
            this.getConnection().close();
                    
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    	
	}
    @Override
    public void remove(int disciplinaId)  {
        
        
        try {
        
            this.criaConexao(true);
                   
                   PreparedStatement stmt = null;
                   stmt = this.getConnection().prepareStatement("delete from disciplina where id ="+  Integer.toString(disciplinaId));
                    stmt.execute();
                   
                   this.getConnection().close();
                    
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    	
	}
    @Override
    public void update(DisciplinaDTO disciplina)
			 {
        try {
            this.criaConexao(false);
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
		LOG.debug("Criou a conex�o!");
		String sql = "update disciplina set nome = ?, codigo = ? where id = ?";
		PreparedStatement stmt = null;
		try {
			stmt = this.getConnection().prepareStatement(sql);
			LOG.debug("PreparedStatement criado com sucesso!");
			stmt.setString(1, disciplina.getNome());
			stmt.setString(2, disciplina.getCodigo());
                        stmt.setInt(3, disciplina.getId());
			int retorno = stmt.executeUpdate();
			if (retorno == 0) {
				this.getConnection().rollback();
				throw new SQLException(
						"Ocorreu um erro inesperado no momento de alterar dados de Revendedor no banco!");
			}
			LOG.debug("Confirmando as altera��es no banco.");
			this.getConnection().commit();

		} catch (SQLException e) {
			LOG.debug("Desfazendo as altera��es no banco.");
            try {
                this.getConnection().rollback();
            } catch (SQLException ex) {
                java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
			LOG.debug("Lan�ando a exce��o da camada de persist�ncia.");
            try {
                throw e;
            } catch (SQLException ex) {
                java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
		} finally {
			try {
				stmt.close();
				this.fechaConexao();
			} catch (SQLException e) {
                try {
                    throw e;
                } catch (SQLException ex) {
                    java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
			}

		}

	}
    @Override
    public ArrayList<DisciplinaDTO> listAll() {
        ArrayList<DisciplinaDTO> lista = null;
        
        try {
        
            this.criaConexao(false);
                   
                   PreparedStatement stmt = null;
                   stmt = this.getConnection().prepareStatement("select id,codigo,nome from disciplina");
                   ResultSet rs = stmt.executeQuery();
                   lista = new ArrayList<DisciplinaDTO>();
                   while(rs.next()){
                       Disciplina d = new Disciplina();
                       d.setId(Integer.parseInt(rs.getString("id")));
                       d.setCodigo(rs.getString("codigo"));
                       d.setNome(rs.getString("nome"));
                       lista.add(new DisciplinaDTO(d));
                   }
                   this.getConnection().close();
                    
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return lista;	
    }
    @Override
    public DisciplinaDTO searchById(int disciplinaId) {
       
        try {
            DisciplinaDTO dto = null;
            this.criaConexao(false);
            String sql = "select id,codigo, nome from Disciplina where id = ?";
            PreparedStatement stmt = null;
            ResultSet rs = null;
            
            try {
                    stmt = this.getConnection().prepareStatement(sql);
                    stmt.setInt(1, disciplinaId);
                    rs = stmt.executeQuery();
                    if (rs.next()) {
                            Disciplina disciplina = new Disciplina();
                            disciplina.setId(rs.getInt("id"));
                            disciplina.setCodigo(rs.getString("codigo"));
                            disciplina.setNome(rs.getString("nome"));
                            dto = new DisciplinaDTO(disciplina);
                    }
            } catch (SQLException e) {
                    throw e;
            } finally {
                    try {
                            stmt.close();
                            rs.close();
                            this.fechaConexao();
                            return dto;
                    } catch (SQLException e) {
                            throw e;
                    }

            }
           
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null; 
    }
    @Override
    public Set<TopicoDTO> listTopicos(int disciplinaId) {
    
        Set<TopicoDTO> lista = null;
        
        try {
        
            this.criaConexao(false);
                   
                   PreparedStatement stmt = null;
                   String sql = "select id,disciplinaid,nome from topico where disciplinaid = ? order by id";
                   stmt = this.getConnection().prepareStatement(sql);
                   stmt.setInt(1, disciplinaId);
                   ResultSet rs = stmt.executeQuery();
                   lista = new HashSet<TopicoDTO>();
                   while(rs.next()){
                       Topico t = new Topico();
                       t.setId(rs.getInt("id"));
                       t.setDisciplinaId(rs.getInt("disciplinaid"));
                       t.setNome(rs.getString("nome"));
                       lista.add(new TopicoDTO(t));
                   }
                   this.getConnection().close();
                    
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return lista;	
    }

    @Override
    public DisciplinaDTO searchByCodigo(String codigo) {
        try {
            DisciplinaDTO dto = null;
            this.criaConexao(false);
            String sql = "select id,codigo, nome from Disciplina where codigo = ?";
            PreparedStatement stmt = null;
            ResultSet rs = null;
            
            try {
                    stmt = this.getConnection().prepareStatement(sql);
                    stmt.setString(1, codigo);
                    rs = stmt.executeQuery();
                    if (rs.next()) {
                            Disciplina disciplina = new Disciplina();
                            disciplina.setId(rs.getInt("id"));
                            disciplina.setCodigo(rs.getString("codigo"));
                            disciplina.setNome(rs.getString("nome"));
                            dto = new DisciplinaDTO(disciplina);
                    }
            } catch (SQLException e) {
                    throw e;
            } finally {
                    try {
                            stmt.close();
                            rs.close();
                            this.fechaConexao();
                            return dto;
                    } catch (SQLException e) {
                            throw e;
                    }

            }
           
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            java.util.logging.Logger.getLogger(PostgresqlDisciplinaDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null; 

            }

    
    

    

   
    
}
